package com.tracker.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.HashSet;
import java.util.Set;

import com.tracker.bean.LoggerPoints;

public class LoggerPointsDAO {

	private Connection conn = null;
	private ResultSet rs = null;
	private Statement stat = null;

	public Set<LoggerPoints> getListObject(String imei, String dateTimeI, String dateTimeF) throws Exception {
		Set<LoggerPoints> list = new HashSet();
		LoggerPoints loggerPoints = null;
		conn = DBSession.getIstance().getConnection();
		String sql = " select id, date, imei, latitude, longitude, speed, typeservice from t06_loggerpoints " +
		 			 " where date >= STR_TO_DATE('" + dateTimeI + "', '%d/%m/%Y %H:%i:%s') and date <= STR_TO_DATE('" + dateTimeF + "', '%d/%m/%Y %H:%i:%s') and imei = '" + imei + "'";
		
		stat = conn.createStatement();
		rs = stat.executeQuery(sql);
		while (rs.next()) {
			loggerPoints = new LoggerPoints();
			loggerPoints.setId(rs.getLong("id"));
			loggerPoints.setDate((new SimpleDateFormat("dd/MM/yyyy")).format(rs.getDate("date")));
			loggerPoints.setTime((new SimpleDateFormat("HH:mm:ss")).format(rs.getDate("date")));
			loggerPoints.setImei(rs.getString("imei"));
			loggerPoints.setLatitude(rs.getString("latitude"));
			loggerPoints.setLongitude(rs.getString("longitude"));
			loggerPoints.setSpeed(rs.getString("speed"));
			loggerPoints.setTypeService(rs.getString("typeservice"));
			list.add(loggerPoints);
		}

		rs.close();
		stat.close();
		
		return list;
	}

	public LoggerPoints getObjectByKey(LoggerPoints paramPoints) throws Exception {
		LoggerPoints loggerPoints = new LoggerPoints();

		conn = DBSession.getIstance().getConnection();
		String sql = " select id, date, imei, latitude, longitude, speed, typeservice from t06_loggerpoints " +
					 " where date = STR_TO_DATE('" + paramPoints.getDate() + " " + paramPoints.getTime() + "', '%d/%m/%Y %H:%i:%s') and imei = '" + paramPoints.getImei() + "'";

		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sql);
		rs.next();
		
		loggerPoints.setId(rs.getLong("id"));
		loggerPoints.setDate((new SimpleDateFormat("dd/MM/yyyy")).format(rs.getDate("date")));
		loggerPoints.setTime((new SimpleDateFormat("HH:mm:ss")).format(rs.getDate("date")));
		loggerPoints.setImei(rs.getString("imei"));
		loggerPoints.setLatitude(rs.getString("latitude"));
		loggerPoints.setLongitude(rs.getString("longitude"));
		loggerPoints.setSpeed(rs.getString("speed"));
		loggerPoints.setTypeService(rs.getString("typeservice"));

		return loggerPoints;
	}

	public LoggerPoints getLastCoord(LoggerPoints paramPoints) throws Exception {
		LoggerPoints loggerPoints = new LoggerPoints();

		conn = DBSession.getIstance().getConnection();
		String sql = " select id, date, imei, latitude, longitude, speed, typeservice from t06_loggerpoints " +
					 " where imei = '" + paramPoints.getImei() + "'";

		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sql);
		if(rs.next()){
			loggerPoints.setId(rs.getLong("id"));
			loggerPoints.setDate((new SimpleDateFormat("dd/MM/yyyy")).format(rs.getDate("date")));
			loggerPoints.setTime((new SimpleDateFormat("HH:mm:ss")).format(rs.getDate("date")));
			loggerPoints.setImei(rs.getString("imei"));
			loggerPoints.setLatitude(rs.getString("latitude"));
			loggerPoints.setLongitude(rs.getString("longitude"));
			loggerPoints.setSpeed(rs.getString("speed"));
			loggerPoints.setTypeService(rs.getString("typeservice"));
		}
		
		return loggerPoints;
	}

	public void persist(LoggerPoints loggerPoints) throws Exception {

		conn = DBSession.getIstance().getConnection();
		String sql = "insert into t06_loggerpoints (date, imei, latitude, longitude, speed, typeservice) values (?,?,?,?,?,?)";

		PreparedStatement stat = conn.prepareStatement(sql);
		stat.setDate(1, new java.sql.Date((new SimpleDateFormat("dd/MM/yyyy HH:mm:ss")).parse(loggerPoints.getDate() + " " + loggerPoints.getTime()).getTime()));
		stat.setString(2, loggerPoints.getImei());
		stat.setString(3, loggerPoints.getLatitude());
		stat.setString(4, loggerPoints.getLongitude());
		stat.setString(5, loggerPoints.getSpeed());
		stat.setString(6, loggerPoints.getTypeService());
		stat.execute();
	}

}
